package com.eascs.web.o2o.web.service;

import com.eascs.app.dao.JdbcSession;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.web.form.LoanBalanceForm;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.inject.Inject;

@Service
public class LoanBalanceService {
	protected final Logger logger = LoggerFactory.getLogger(getClass());

	@Inject
	JdbcSession jdbcSession;

	@Inject
	JdbcSession jdbcSessionO2OService;

    @Inject
    JdbcSession jdbcSessionBiods;

	public PageResult<Object[]> LoanBalancePlatProgress(LoanBalanceForm form) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, form.getPageSize(), form.getPageIndex());
		StringBuffer querySql = new StringBuffer("select l.item_name province,p.item_name city, s.platform_name ,s.loan_amount,s.outstanding_of_deposits,s.defaultcapital,s.compensation,s.compensatory_balance "
				+ "from  (SELECT h.natural_province_code natural_province_code,h.natural_city_code natural_city_code,h.platform_name,sum(t.loan_amount) loan_amount,sum(t.outstanding_of_deposits) outstanding_of_deposits,sum(t.defaultcapital) defaultcapital,sum(t.compensation) compensation,sum(t.compensatory_balance) compensatory_balance FROM ods_o2oservice_balance_his t INNER JOIN ods_o2oservice_platform_regional h ON t.ltd = h.platform_id where 1=1 "); 
		if (StringUtil.isNotBlank(form.getSelectDate())) {
			querySql.append(" AND t.create_date = ?");
			sqlPageQuery.addParam(form.getSelectDate());
		}else{
			querySql.append(" AND t.create_date = DATE_FORMAT(now(),'%Y,%m,%d') ");
		}
		if (StringUtil.isNotBlank(form.getLtd())) {
			querySql.append(" AND t.ltd = ?");
			sqlPageQuery.addParam(form.getLtd());
		}
		querySql.append(" group by h.natural_province_code,h.natural_city_code, t.ltd order by h.natural_province_code,h.natural_city_code, t.ltd) s inner join ods_o2oservice_pub_dditem p ON s.natural_city_code = p.item_code  AND p.is_lock='N' AND p.dd_no = 160 inner join ods_o2oservice_pub_dditem l ON s.natural_province_code = l.item_code AND l.dd_no = 159");
		sqlPageQuery.setQuerySql(querySql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] {3,4,5,6,7});
		return jdbcSessionBiods.findDataWithPage(sqlPageQuery);
	}

	public PageResult<Object[]> LoanBalanceCityProgress(LoanBalanceForm form) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, form.getPageSize(), form.getPageIndex());
		StringBuffer querySql = new StringBuffer("select l.item_name province,p.item_name city ,s.loan_amount,s.outstanding_of_deposits,s.defaultcapital,s.compensation,s.compensatory_balance "
				+ "from  (SELECT h.natural_province_code natural_province_code,h.natural_city_code natural_city_code,sum(t.loan_amount) loan_amount,sum(t.outstanding_of_deposits) outstanding_of_deposits,sum(t.defaultcapital) defaultcapital,sum(t.compensation) compensation,sum(t.compensatory_balance) compensatory_balance FROM ods_o2oservice_balance_his t INNER JOIN ods_o2oservice_platform_regional h ON t.ltd = h.platform_id where 1=1 "); 
		if (StringUtil.isNotBlank(form.getSelectDate())) {
			querySql.append(" AND t.create_date = ?");
			sqlPageQuery.addParam(form.getSelectDate());
		}else{
			querySql.append(" AND t.create_date = DATE_FORMAT(now(),'%Y,%m,%d') ");
		}
		if (StringUtil.isNotBlank(form.getProvince())) {
			querySql.append(" AND h.natural_province_code = ?");
			sqlPageQuery.addParam(form.getProvince());
		}
		if (StringUtil.isNotBlank(form.getCity())) {
			querySql.append(" AND h.natural_city_code = ?");
			sqlPageQuery.addParam(form.getCity());
		}
		querySql.append(" group by h.natural_province_code,h.natural_city_code order by h.natural_province_code,h.natural_city_code) s inner join ods_o2oservice_pub_dditem p ON s.natural_city_code = p.item_code  AND p.is_lock='N' AND p.dd_no = 160 inner join ods_o2oservice_pub_dditem l ON s.natural_province_code = l.item_code AND l.dd_no = 159");
		sqlPageQuery.setQuerySql(querySql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] {2,3,4,5,6});
		return jdbcSessionBiods.findDataWithPage(sqlPageQuery);
	}
	
	public PageResult<Object[]> LoanBalanceProvinceProgress(LoanBalanceForm form) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, form.getPageSize(), form.getPageIndex());
		StringBuffer querySql = new StringBuffer("select l.item_name province,s.loan_amount,s.outstanding_of_deposits,s.defaultcapital,s.compensation,s.compensatory_balance "
				+ "from  (SELECT h.natural_province_code natural_province_code,sum(t.loan_amount) loan_amount,sum(t.outstanding_of_deposits) outstanding_of_deposits,sum(t.defaultcapital) defaultcapital,sum(t.compensation) compensation,sum(t.compensatory_balance) compensatory_balance FROM ods_o2oservice_balance_his t INNER JOIN ods_o2oservice_platform_regional h ON t.ltd = h.platform_id where 1=1 "); 
		if (StringUtil.isNotBlank(form.getSelectDate())) {
			querySql.append(" AND t.create_date = ?");
			sqlPageQuery.addParam(form.getSelectDate());
		}else{
			querySql.append(" AND t.create_date = DATE_FORMAT(now(),'%Y,%m,%d') ");
		}
		if (StringUtil.isNotBlank(form.getProvince())) {
			querySql.append(" AND h.natural_province_code = ?");
			sqlPageQuery.addParam(form.getProvince());
		}
		querySql.append(" group by h.natural_province_code order by h.natural_province_code) s inner join ods_o2oservice_pub_dditem l ON s.natural_province_code = l.item_code AND l.dd_no = 159");
		sqlPageQuery.setQuerySql(querySql.toString());
		sqlPageQuery.setSumColumnIndexs(new int[] {1,2,3,4,5});
		return jdbcSessionBiods.findDataWithPage(sqlPageQuery);
	}
	
	/*
	public PageResult<Object[]> LoanBalanceProgress(LoanBalanceForm form) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, form.getPageSize(), form.getPageIndex());
		StringBuffer querySql = new StringBuffer("select l.item_name province,p.item_name city,loan_amount,outstanding_of_deposits,defaultcapital,compensation,compensatory_balance "
				+ "from  (SELECT h.PROVINCE,h.CITY,sum(t.loan_amount) loan_amount,sum(t.outstanding_of_deposits) outstanding_of_deposits,sum(t.defaultcapital) defaultcapital,sum(t.compensation) compensation,sum(t.compensatory_balance) compensatory_balance FROM ods_o2oservice_balance_his t INNER JOIN hr_units h ON t.ltd = h.UNITNO where 1=1 "); 
		if (StringUtil.isNotBlank(form.getSelectDate())) {
			querySql.append(" AND t.create_date = ?");
			sqlPageQuery.addParam(form.getSelectDate());
		}else{
			querySql.append(" AND t.create_date = now() ");
		}
		if (StringUtil.isNotBlank(form.getProvince())) {
			querySql.append(" AND h.PROVINCE = ?");
			sqlPageQuery.addParam(form.getProvince());
		}
		if (StringUtil.isNotBlank(form.getCity())) {
			querySql.append(" AND h.CITY = ?");
			sqlPageQuery.addParam(form.getCity());
		}
		if (StringUtil.isNotBlank(form.getLtd())) {
			querySql.append(" AND t.ltd = ?");
			sqlPageQuery.addParam(form.getLtd());
		}
		querySql.append(" group by h.PROVINCE, h.city,t.ltd order by h.PROVINCE, h.city) s inner join ods_o2oservice_pub_dditem p ON s.city = p.item_code AND p.dd_no = 160 inner join ods_o2oservice_pub_dditem l ON s.PROVINCE = l.item_code AND l.dd_no = 159");
		sqlPageQuery.setQuerySql(querySql.toString());
		return jdbcSessionO2OService.findDataWithPage(sqlPageQuery);
	}*/

	
}
